package Messages;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import ocsf.server.ConnectionToClient;
import server.SQLConnection;

/**
 * this class implements the workers remove book from db
 * @author Ron Shenkerman
 * @author Gabi Revah
 * @author Aviad Almog
 * @author Ivan Onyshchenko
 */
public class MessageRemovebook {
	public String bookid,title,bid;
	public int i,j,numofbooks;
	PreparedStatement ps;
	ResultSet rset;
	private ConnectionToClient client ;
/**
 * 
 * @return the book id 
 */
	public String getId() {
		return bookid;
	}
/**
 * 
 * @param setting the book id 
 */
	public void setId(String id) {
		this.bookid = id;
	}
/**
 * 
 * @return the title 
 */
	public String getTitle() {
		return title;
	}
/**
 * 
 * @param setting the title of the book 
 */
	public void setTitle(String title) {
		this.title = title;
	}
/**
 * constractor of this class 
 * @param client
 */
	public MessageRemovebook(ConnectionToClient client) {
		super();
		this.client=client;
		}
	/**
	 * view book information from the DB 
	 * @param str
	 */
	@SuppressWarnings("null")
	public void viewbook(String[] str){
		
		String[] msg = null;
		try 
		{
			System.out.println("checking condition for removing a book");
			Statement stmt,stmt1;
			SQLConnection conn = new  SQLConnection();
			stmt = conn.getConnection().createStatement();
			stmt1 = conn.getConnection().createStatement();
			ResultSet rs= stmt.executeQuery("SELECT * FROM db1.lending WHERE CopyID='"+str[1]+"' AND lend='"+"yes"+"';");
				try {
				
						if (rs.next())//book exists cant remove
							{
										String s="CantRemove";
										client.sendToClient(s);
							}		
										else{//book doesnt exsist in lending removing...
											rset= stmt1.executeQuery("SELECT * FROM db1.books,db1.bookscopies  WHERE  bookscopies.CopyId='"+str[1]+"' and books.ID=bookscopies.BID ;");
											rset.next();
											numofbooks=rset.getInt(8);
											bid=rset.getString(1);
												if(numofbooks==1){
													System.out.print("removing book..."); 
													ps=conn.getConnection().prepareStatement("DELETE FROM db1.books where ID = ?");
													ps.setString(1,bid);
													ps.executeUpdate();
													ps=conn.getConnection().prepareStatement("DELETE FROM db1.bookscopies where CopyId= ?;");
													ps.setString(1, str[1]);
													ps.executeUpdate();
													ps=conn.getConnection().prepareStatement("DELETE FROM db1.bookscategory where BookId= ?;");
													ps.setString(1,bid);
													ps.executeUpdate();
													System.out.print("book removed!"); 
													ArrayList<String> ViewArr=new ArrayList<String>();
													ViewArr.add("RemoveBook");
													ViewArr.add(bookid);
													ViewArr.add(title);	
													client.sendToClient(ViewArr);
											}
											else{
												ps=conn.getConnection().prepareStatement("DELETE FROM db1.bookscopies where CopyId = ?;");
												ps.setString(1,str[1]);
												ps.executeUpdate();
												System.out.print("book removed!"); 
												ArrayList<String> ViewArr=new ArrayList<String>();
												ViewArr.add("RemoveBook");
												ViewArr.add(bookid);
												ViewArr.add(title);	
												java.sql.PreparedStatement ps1 = conn.getConnection().prepareStatement("Update DB1.books SET NumberOfCopies=? WHERE ID = ?");
												ps1.setInt(1,(numofbooks-1));
												ps1.setString(2,bid);
												ps1.executeUpdate();
												client.sendToClient(ViewArr);
											}
							
											
					}
								
		}
  
		
				catch (SQLException e)
				{	
				e.printStackTrace();
				}


	
	
	
	
	}
		catch (Exception ex )
		{
	  ex.printStackTrace();
		}
}
}
